package data
{
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.errors.SQLError;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	
	import mx.collections.ArrayCollection;

	public class DatabaseConnector
	{
		private var conn:SQLConnection = new SQLConnection();
		private const DB_NAME:String = "Database.db";
		private const TABLE_NAME:String = "browsing_history";
		
		public function DatabaseConnector()
		{
			var dbFile:File = File.applicationStorageDirectory.resolvePath(DB_NAME);
			try
			{
				// Create database if not exists
				conn.open(dbFile);
				trace("the database was created successfully");
				
				// Create table if not exists
				var createStmt:SQLStatement = new SQLStatement();
				createStmt.sqlConnection = conn;
				var sql:String =
					"CREATE TABLE IF NOT EXISTS " + TABLE_NAME +
				" (history_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
					" url TEXT" +
					")";
				createStmt.text = sql;
				
				try
				{
					createStmt.execute();
					trace("Table created");
				}
				catch(error:SQLError)
				{
					trace("Table create Error message:", error.message);
					trace("Details:", error.details);
				}
			}
			catch(error:SQLError)
			{
				trace("database create Error message:", error.message);
				trace("Details:", error.details);
			}
		}
		
		public function addUrl(url:String):Boolean
		{
			var insertStmt:SQLStatement = new SQLStatement();
			insertStmt.sqlConnection = conn;
			var sql:String =
				"INSERT INTO " + TABLE_NAME +
				" (url) " +
				"VALUES ('" + url +
				"')";
			insertStmt.text = sql;
			
			try
			{
				// execute the statement
				insertStmt.execute();
				trace("INSERT statement succeeded");
				return true;
			}
			catch (error:SQLError)
			{
				trace("INSERT statement Error message:", error.message);
				trace("Details:", error.details);
			}
			return false;
		}
		
		public function getUrls():ArrayCollection
		{
			var temp:ArrayCollection = new ArrayCollection();

			var selectString:String = "SELECT * FROM "+TABLE_NAME;
			var selectData:SQLStatement = new SQLStatement();
			selectData.sqlConnection = conn;
			selectData.text = selectString;
			
			try
			{
				selectData.execute();
				var result:SQLResult = selectData.getResult();
				var numResults:int = result.data.length;
				for (var i:int = 0; i < numResults; i++)
				{
					var row:Object = result.data[i];
					trace(row.url);
					temp.addItem(row.url);
				}
			}
			catch(error:SQLError)
			{
				trace("selectData Error message:", error.message);
				trace("Details:", error.details);
			}
			finally
			{
				return temp;
			}
		}
	}
}